"""
cron: 0 */6 * * *
new Env('应用商店排行采集');
"""
import requests
import os
import mysql.connector
from datetime import datetime

# 获取环境变量
host = os.environ.get('HOST')
user = os.environ.get('USER')
password = os.environ.get('PASSWORD')
database = os.environ.get('DATABASE')

print("正在获取环境变量...")
print(f"数据库地址：{host}")
print(f"数据库用户名：{user}")
print(f"数据库密码：{password}")
print(f"数据库名称：{database}")

# 连接数据库
connection = mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

cursor = connection.cursor()

# 创建表（如果不存在）
create_table_query = """
CREATE TABLE IF NOT EXISTS ningm_app_rank (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_name VARCHAR(200) NOT NULL,
    developer VARCHAR(200),
    category VARCHAR(50),
    platform VARCHAR(50),
    rank_type VARCHAR(50),
    rank_value INT,
    price DECIMAL(10,2),
    rating DECIMAL(3,1),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_platform (platform),
    INDEX idx_rank_type (rank_type),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""
cursor.execute(create_table_query)

# 删除旧数据（保留1天内的数据）
print("删除旧数据...")
delete_query = "DELETE FROM ningm_app_rank WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 DAY)"
cursor.execute(delete_query)

def get_appstore_rank():
    """获取App Store排行榜数据"""
    try:
        headers = {
            'User-Agent': 'Mozilla/5.0 (iPhone; CPU iPhone OS 14_7_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.2 Mobile/15E148 Safari/604.1'
        }
        
        # App Store热门免费榜
        url = 'https://itunes.apple.com/cn/rss/topfreeapplications/limit=100/json'
        response = requests.get(url, headers=headers)
        
        if response.status_code == 200:
            data = response.json()
            apps = data.get('feed', {}).get('entry', [])
            
            for rank, app in enumerate(apps[:20], 1):
                app_name = app.get('im:name', {}).get('label', '')
                developer = app.get('im:artist', {}).get('label', '')
                category = app.get('category', {}).get('attributes', {}).get('label', '')
                rating = float(app.get('im:rating', {}).get('label', '0'))
                
                print(f"正在保存App Store应用数据: {app_name}")
                insert_query = """
                    INSERT INTO ningm_app_rank 
                    (app_name, developer, category, platform, rank_type, rank_value, price, rating) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    app_name,
                    developer,
                    category,
                    'appstore',
                    'free',
                    rank,
                    0.00,
                    rating
                )
                cursor.execute(insert_query, insert_values)
                
    except Exception as e:
        print(f"获取App Store排行榜失败: {str(e)}")

def get_huawei_rank():
    """获取华为应用市场排行榜数据"""
    try:
        headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
        }
        
        # 华为应用市场热门榜
        url = 'https://appstore.huawei.com/api/rank/v2/list'
        params = {
            'rankId': '1',
            'pageSize': '20',
            'pageNo': '1'
        }
        
        response = requests.get(url, headers=headers, params=params)
        
        if response.status_code == 200:
            data = response.json()
            apps = data.get('rankingList', [])
            
            for rank, app in enumerate(apps, 1):
                app_name = app.get('appName', '')
                developer = app.get('developer', '')
                category = app.get('categoryName', '')
                rating = float(app.get('rating', 0))
                
                print(f"正在保存华为应用市场数据: {app_name}")
                insert_query = """
                    INSERT INTO ningm_app_rank 
                    (app_name, developer, category, platform, rank_type, rank_value, price, rating) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    app_name,
                    developer,
                    category,
                    'huawei',
                    'hot',
                    rank,
                    0.00,
                    rating
                )
                cursor.execute(insert_query, insert_values)
                
    except Exception as e:
        print(f"获取华为应用市场排行榜失败: {str(e)}")

# 获取并保存排行榜数据
print("开始获取应用商店排行榜数据...")
get_appstore_rank()
get_huawei_rank()

connection.commit()
cursor.close()
connection.close()

print("数据保存完成！") 